package Program.SqlTool;


import Program.Information.*;
import Program.Tool.DruidJDBC;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;


public class AdminDAO {
    //数据库连接对象
    private static Connection conn;
    //sql执行对象
    private static PreparedStatement pstmt;
    //结果集对象
    private static ResultSet rs;


    //查询相关方法

    //输入的检查账号是否正确
    public static boolean accountCheck(String adminName, String adminPassword) {

        //sql语句
        String sql = "select * from admin where adminName = ? and adminPassword = ?";
        try {
            //获取数据库连接
            conn = DruidJDBC.getConnection();
            //sql语句执行对象的设置
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, adminName);
            pstmt.setString(2, adminPassword);
            //执行sql
            rs = pstmt.executeQuery();
            if (rs.next()) {
                //登录成功
                return true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidJDBC.close(rs, pstmt, conn);
        }
        //登录失败
        return false;
    }

    //获取当前管理员账号所有信息
    public static Admin selectAdmin(String adminName) {
        //sql语句
        String sql = "select * from admin where adminName = ?";
        //创建Admin对象记录数据
        Admin admin = null;
        try {
            //获取数据库连接
            conn = DruidJDBC.getConnection();
            //sql语句执行对象的设置
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, adminName);
            //执行sql
            rs = pstmt.executeQuery();
            while (rs.next()) {
                // 获得id值
                int adminID = rs.getInt("adminID");
                // 获得密码
                String adminPassword = rs.getString("adminPassword");
                //获取名字
                String name = rs.getString("name");
                // 获得性别
                String adminSex = rs.getString("adminSex");
                // 获得住址
                String adminAddress = rs.getString("adminAddress");
                admin = new Admin(adminID, adminName, adminPassword, name, adminSex, adminAddress);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidJDBC.close(rs, pstmt, conn);
        }
        return admin;
    }

    //获取所有用户账号所有信息
    public static ArrayList<User> selectUsers() {
        //sql语句
        String sql = "select * from `user`";
        //创建用户对象记录数据
        User user = null;
        //创建集合记录所有的用户
        ArrayList<User> list = new ArrayList<>();

        try {
            //获取数据库连接
            conn = DruidJDBC.getConnection();
            //sql语句执行对象的设置
            pstmt = conn.prepareStatement(sql);
            //执行sql
            rs = pstmt.executeQuery();
            while (rs.next()) {
                // 获得id值
                int userID = rs.getInt("userID");
                //获取用户名
                String userName = rs.getString("userName");
                // 获得密码
                String userPassword = rs.getString("userPassword");
                //获取名字
                String name = rs.getString("name");
                // 获得性别
                String userSex = rs.getString("userSex");
                //获取用户类型
                String usertype = rs.getString("usertype");
                //获取手机
                String phone = rs.getString("phone");
                //获取预存款
                double adDeposit = rs.getDouble("adDeposit");
                // 获得地址
                String place = rs.getString("place");
                //获取用户状态
                String state = rs.getString("state");

                user = new User(userID, userName, userPassword, name, userSex, usertype, phone, adDeposit, place, state);
                list.add(user);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidJDBC.close(rs, pstmt, conn);
        }
        return list;
    }

    //获取所有收费记录
    public static ArrayList<Fee> getFees() {
        ArrayList<Fee> list = new ArrayList<>();
        //创建视图sql语句
        String sql1 = "CREATE OR REPLACE VIEW `Fees` AS " +
                "SELECT `usage`.`id` AS '账单编号'," +
                "       `usage`.`userID` AS '用户ID'," +
                "       ROUND(`usage`.`waterUsage` * `usertype_fee`.`waterFee`, 2) AS '水费'," +
                "       ROUND(`usage`.`electricUsage` * `usertype_fee`.`electricFee`, 2) AS '电费'," +
                "       ROUND(`usage`.`gasUsage` * `usertype_fee`.`gasFee`, 2) AS '天然气费'," +
                "       ROUND(`usage`.`parkDay` * `usertype_fee`.`parkFee`, 2) AS '停车费'," +
                "       `usage`.`day` AS '日期'," +
                "       ROUND(ROUND(`usage`.`waterUsage` * `usertype_fee`.`waterFee`, 2)+ROUND(`usage`.`electricUsage` * `usertype_fee`.`electricFee`, 2)+ROUND(`usage`.`gasUsage` * `usertype_fee`.`gasFee`, 2)+ROUND(`usage`.`parkDay` * `usertype_fee`.`parkFee`, 2),2) AS '总费用'," +
                "       `usage`.`state` AS '支付状态'" +
                "FROM `usage`" +
                "JOIN `user` ON `usage`.`userID` = `user`.`userID`" +
                "JOIN `usertype_fee` ON `usertype_fee`.`usertype` = `user`.`usertype`";
        //查询视图sql语句
        String sql2 = "SELECT * FROM `Fees`";

        try {
            //获取数据库连接
            conn = DruidJDBC.getConnection();
            //创建视图
            //sql语句执行对象的设置
            pstmt = conn.prepareStatement(sql1);
            //执行创建视图sql语句
            pstmt.executeUpdate();
            //sql语句执行对象的设置
            //查询视图
            pstmt = conn.prepareStatement(sql2);
            //执行查询视图sql语句
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                //账单编号
                int id = rs.getInt("账单编号");
                //用户id
                int userID = rs.getInt("用户ID");
                //水费
                double waterFee = rs.getDouble("水费");
                //电费
                double electricFee = rs.getDouble("电费");
                //天然气费
                double gasFee = rs.getDouble("天然气费");
                //停车费
                int parkFee = rs.getInt("停车费");
                //日期
                String day = rs.getString("日期");
                //总费用
                double sumFee = rs.getDouble("总费用");
                //获取支付状态
                String state = rs.getString("支付状态");
                Fee fee = new Fee(id, userID, waterFee, electricFee, gasFee, parkFee, day, sumFee, state);
                list.add(fee);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidJDBC.close(rs, pstmt, conn);
        }
        return list;
    }

    //获取所有报修单
    public static ArrayList<Repair> getRepairs() {
        //sql语句
        String sql = "select * from `repair`";
        //创建报修对象记录数据
        Repair repair = null;
        //创建集合记录所有的报修单
        ArrayList<Repair> list = new ArrayList<>();
        try {
            //获取数据库连接
            conn = DruidJDBC.getConnection();
            //sql语句执行对象的设置
            pstmt = conn.prepareStatement(sql);
            //执行sql
            rs = pstmt.executeQuery();
            while (rs.next()) {
                //保修单编号
                int id = rs.getInt("id");
                //用户ID
                int userID = rs.getInt("userID");
                //物品
                String article = rs.getString("article");
                //住址
                String place = rs.getString("place");
                //报修时间
                String startTime = rs.getString("startTime");
                //维修时间
                String endTime = rs.getString("endTime");
                //报修详情
                String details = rs.getString("details");
                //处理状态
                String state = rs.getString("state");

                repair = new Repair(id, userID, article, place, startTime, endTime, details, state);
                list.add(repair);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidJDBC.close(rs, pstmt, conn);
        }
        return list;
    }

    //获取所有投诉单
    public static ArrayList<Complaint> getComplaints() {
        //sql语句
        String sql = "select * from `complaint`";
        //创建投诉象记录数据
        Complaint complaint = null;
        //创建集合记录所有的投诉单
        ArrayList<Complaint> list = new ArrayList<>();
        //该用户的报修单显示编号
        try {
            //获取数据库连接
            conn = DruidJDBC.getConnection();
            //sql语句执行对象的设置
            pstmt = conn.prepareStatement(sql);
            //执行sql
            rs = pstmt.executeQuery();
            while (rs.next()) {
                //保修单编号
                int id = rs.getInt("id");
                //用户ID
                int userID = rs.getInt("userID");
                //报修时间
                String startTime = rs.getString("startTime");
                //维修时间
                String endTime = rs.getString("endTime");
                //报修详情
                String details = rs.getString("details");
                //处理状态
                String state = rs.getString("state");

                complaint = new Complaint(id, userID, startTime, endTime, details, state);
                list.add(complaint);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidJDBC.close(rs, pstmt, conn);
        }
        return list;
    }


    //检查是否存在该月的账单
    public static boolean checkDate(int userID, String date) {
        //sql语句
        String sql = "select * from `usage` WHERE userID = ? AND day = ?";

        try {
            //获取数据库连接
            conn = DruidJDBC.getConnection();
            //sql语句执行对象的设置
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, userID);
            pstmt.setString(2, date);
            //执行sql
            rs = pstmt.executeQuery();
            if (rs.next()) {
                return true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidJDBC.close(rs, pstmt, conn);
        }
        return false;
    }

    //获取所有充值账单
    public static ArrayList<Recharge> getRecharges() {
        //sql语句
        String sql = "select * from `recharge`";
        //创建充值单对象记录数据
        Recharge recharge = null;
        //创建集合记录所有的充值单
        ArrayList<Recharge> list = new ArrayList<>();
        try {
            //获取数据库连接
            conn = DruidJDBC.getConnection();
            //sql语句执行对象的设置
            pstmt = conn.prepareStatement(sql);
            //执行sql
            rs = pstmt.executeQuery();
            while (rs.next()) {
                //保修单编号
                int id = rs.getInt("id");
                //用户ID
                int userID = rs.getInt("userID");
                //充值时间
                String startTime = rs.getString("startTime");
                //金额
                Double amount = rs.getDouble("amount");
                //支付状态
                String state = rs.getString("state");

                recharge = new Recharge(id, userID, startTime, amount, state);
                list.add(recharge);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidJDBC.close(rs, pstmt, conn);
        }
        return list;
    }


    //增删改相关方法

    //修改用户账号状态
    public static boolean modifyUserState(int userID, String state) {
        //sql语句
        String sql = "UPDATE `user` SET state = ? WHERE state = ? AND userID = ?";

        try {
            //获取数据库连接
            conn = DruidJDBC.getConnection();
            //sql语句执行对象的设置
            pstmt = conn.prepareStatement(sql);
            if (state.equals("0")) {
                pstmt.setString(1, "1");
                pstmt.setString(2, state);
            } else {
                pstmt.setString(1, "0");
                pstmt.setString(2, state);
            }
            pstmt.setInt(3, userID);
            int i = pstmt.executeUpdate();
            if (i == 1) {
                return true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidJDBC.close(pstmt, conn);
        }
        return false;

    }

    //添加新的管理员账号
    public static boolean addNewAdmin(String adminName, String adminPassword, String name,
                                      String adminSex, String adminAddress) {
        //sql语句
        String sql = "insert into admin(adminID,adminName,adminPassword,name,adminSex,adminAddress)" +
                " VALUES (null,?,?,?,?,?)";
        try {
            //获取数据库连接
            conn = DruidJDBC.getConnection();
            //sql语句执行对象的设置
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, adminName);
            pstmt.setString(2, adminPassword);
            pstmt.setString(3, name);
            pstmt.setString(4, adminSex);
            pstmt.setString(5, adminAddress);
            //执行sql
            int i = pstmt.executeUpdate();
            if (i == 1) {
                //创建成功
                return true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidJDBC.close(pstmt, conn);
        }
        //创建失败
        return false;
    }

    //删除管理员账号
    public static boolean deleteAdmin(String adminName) {
        //sql语句
        String sql = "delete from admin where adminName = ?";
        try {
            //获取数据库连接
            conn = DruidJDBC.getConnection();
            //sql语句执行对象的设置
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, adminName);
            //执行sql
            int i = pstmt.executeUpdate();
            if (i == 1) {
                AdminDAO.updateID();
                //删除成功
                return true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidJDBC.close(pstmt, conn);
        }
        //删除失败
        return false;
    }

    //更新自增id
    public static void updateID() {
        //更新自增id的sql语句
        String sql1 = "SET @auto_id = 0";
        String sql2 = "UPDATE admin SET adminID = (@auto_id := @auto_id + 1)";
        String sql3 = "ALTER TABLE admin AUTO_INCREMENT = 1";

        try {
            //获取数据库连接
            conn = DruidJDBC.getConnection();
            //sql语句执行对象的设置
            pstmt = conn.prepareStatement(sql1);
            pstmt.executeUpdate();
            pstmt = conn.prepareStatement(sql2);
            pstmt.executeUpdate();
            pstmt = conn.prepareStatement(sql3);
            pstmt.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidJDBC.close(pstmt, conn);
        }
    }


    //更改管理员账号密码
    public static boolean updatePassword(String adminName, String adminPassword) {
        //sql语句
        String sql = "update admin set adminPassword = ? where adminName = ?";
        try {
            //获取数据库连接
            conn = DruidJDBC.getConnection();
            //sql语句执行对象的设置
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, adminPassword);
            pstmt.setString(2, adminName);
            //执行sql
            int i = pstmt.executeUpdate();
            if (i == 1) {
                //更改成功
                return true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidJDBC.close(pstmt, conn);
        }
        //更改失败
        return false;
    }

    //修改用户账单的状态
    public static boolean modifyState(int id, String state) {
        //sql语句
        String sql = "UPDATE `usage` SET state = ? WHERE state = ? AND id = ?";

        try {
            //获取数据库连接
            conn = DruidJDBC.getConnection();
            //sql语句执行对象的设置
            pstmt = conn.prepareStatement(sql);
            if (state.equals("0")) {
                pstmt.setString(1, "1");
                pstmt.setString(2, state);
            } else {
                pstmt.setString(1, "0");
                pstmt.setString(2, state);
            }
            pstmt.setInt(3, id);
            int i = pstmt.executeUpdate();
            if (i == 1) {
                return true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidJDBC.close(pstmt, conn);
        }
        return false;
    }

    //修改报修单的状态
    public static boolean modifyRepairState(int id, String state) {
        //sql语句
        String sql = "UPDATE `repair` SET state = ? WHERE state = ? AND id = ?";

        try {
            //获取数据库连接
            conn = DruidJDBC.getConnection();
            //sql语句执行对象的设置
            pstmt = conn.prepareStatement(sql);
            if (state.equals("0")) {
                pstmt.setString(1, "1");
                pstmt.setString(2, state);
            } else if (state.equals("1")) {
                pstmt.setString(1, "2");
                pstmt.setString(2, state);
            } else {
                pstmt.setString(1, "0");
                pstmt.setString(2, state);
            }
            pstmt.setInt(3, id);
            int i = pstmt.executeUpdate();
            if (i == 1) {
                return true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidJDBC.close(pstmt, conn);
        }
        return false;
    }

    //修改报修单的处理时间
    public static boolean modifyRepairTime(int id, String endTime, String state) {
        //sql语句
        String sql = "UPDATE `repair` SET endTime = ? WHERE  id = ?";

        try {
            //获取数据库连接
            conn = DruidJDBC.getConnection();
            //sql语句执行对象的设置
            pstmt = conn.prepareStatement(sql);
            if (state.equals("2")) {
                pstmt.setString(1, "---");
            } else {
                pstmt.setString(1, endTime);
            }
            pstmt.setInt(2, id);
            int i = pstmt.executeUpdate();
            if (i == 1) {
                return true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidJDBC.close(pstmt, conn);
        }
        return false;
    }

    //修改投诉单的状态
    public static boolean modifyCptState(int id, String state) {
        //sql语句
        String sql = "UPDATE `complaint` SET state = ? WHERE state = ? AND id = ?";

        try {
            //获取数据库连接
            conn = DruidJDBC.getConnection();
            //sql语句执行对象的设置
            pstmt = conn.prepareStatement(sql);
            if (state.equals("0")) {
                pstmt.setString(1, "1");
                pstmt.setString(2, state);
            } else if (state.equals("1")) {
                pstmt.setString(1, "2");
                pstmt.setString(2, state);
            } else {
                pstmt.setString(1, "0");
                pstmt.setString(2, state);
            }
            pstmt.setInt(3, id);
            int i = pstmt.executeUpdate();
            if (i == 1) {
                return true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidJDBC.close(pstmt, conn);
        }
        return false;
    }

    //修改报修单的处理时间
    public static boolean modifyCptTime(int id, String endTime, String state) {
        //sql语句
        String sql = "UPDATE `complaint` SET endTime = ? WHERE  id = ?";

        try {
            //获取数据库连接
            conn = DruidJDBC.getConnection();
            //sql语句执行对象的设置
            pstmt = conn.prepareStatement(sql);
            if (state.equals("2")) {
                pstmt.setString(1, "---");
            } else {
                pstmt.setString(1, endTime);
            }
            pstmt.setInt(2, id);
            int i = pstmt.executeUpdate();
            if (i == 1) {
                return true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidJDBC.close(pstmt, conn);
        }
        return false;
    }

    //修改充值单的状态
    public static boolean modifyRCState(int id, String state) {
        //sql语句
        String sql = "UPDATE `recharge` SET state = ? WHERE state = ? AND id = ?";

        try {
            //获取数据库连接
            conn = DruidJDBC.getConnection();
            //sql语句执行对象的设置
            pstmt = conn.prepareStatement(sql);
            if (state.equals("0")) {
                pstmt.setString(1, "1");
                pstmt.setString(2, state);
            } else {
                pstmt.setString(1, "0");
                pstmt.setString(2, state);
            }
            pstmt.setInt(3, id);
            int i = pstmt.executeUpdate();
            if (i == 1) {
                return true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidJDBC.close(pstmt, conn);
        }
        return false;
    }

    //添加预存款金额
    public static boolean modifyAmount(int userID, double adDeposit, double lastAdDeposit) {
        //sql语句
        String sql = "UPDATE `user` SET adDeposit = ? WHERE userID = ?";
        try {
            //获取数据库连接
            conn = DruidJDBC.getConnection();
            //sql语句执行对象的设置
            pstmt = conn.prepareStatement(sql);
            pstmt.setDouble(1, lastAdDeposit + adDeposit);
            pstmt.setInt(2, userID);
            int i = pstmt.executeUpdate();
            if (i == 1) {
                return true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidJDBC.close(pstmt, conn);
        }

        return false;
    }

    //撤回预存款金额
    public static boolean withdrawAmount(int userID, double adDeposit, double lastAdDeposit) {
        //sql语句
        String sql = "UPDATE `user` SET adDeposit = ? WHERE userID = ?";
        try {
            //获取数据库连接
            conn = DruidJDBC.getConnection();
            //sql语句执行对象的设置
            pstmt = conn.prepareStatement(sql);
            pstmt.setDouble(1, lastAdDeposit - adDeposit);
            pstmt.setInt(2, userID);
            int i = pstmt.executeUpdate();
            if (i == 1) {
                return true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidJDBC.close(pstmt, conn);
        }
        return false;
    }

    //生成用量账单
    public static boolean addUsage(int userID, String waterUsage, String electricUsage, String gasUsage, String
            parkDay, String day) {
        //sql语句
        String sql = "INSERT INTO `usage` (id,userID,waterUsage,electricUsage,gasUsage,parkDay,day,state)" +
                "VALUES (NULL,?,?,?,?,?,?,'0')";

        //获取数据库连接
        try {
            conn = DruidJDBC.getConnection();
            //sql语句执行对象的设置
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, userID);
            pstmt.setString(2, waterUsage);
            pstmt.setString(3, electricUsage);
            pstmt.setString(4, gasUsage);
            pstmt.setString(5, parkDay);
            pstmt.setString(6, day);
            int i = pstmt.executeUpdate();
            if (i == 1) {
                return true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidJDBC.close(pstmt, conn);
        }
        return false;
    }

    //删除用户的所有账单
    public static boolean delFees(int userID) {
        //sql语句
        String sql = "DELETE FROM `usage` WHERE `usage`.userID = ?";
        //获取数据库连接
        try {
            conn = DruidJDBC.getConnection();
            //sql语句执行对象的设置
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, userID);
            int i = pstmt.executeUpdate();
            if (i == 1) {
                return true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidJDBC.close(pstmt, conn);
        }
        return false;
    }

    //删除用户的所有报修单
    public static boolean delRepairs(int userID) {
        //sql语句
        String sql = "DELETE FROM `repair` WHERE `repair`.userID = ?";
        //获取数据库连接
        try {
            conn = DruidJDBC.getConnection();
            //sql语句执行对象的设置
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, userID);
            int i = pstmt.executeUpdate();
            if (i == 1) {
                return true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidJDBC.close(pstmt, conn);
        }
        return false;
    }

    //删除用户的所有投诉单
    public static boolean delComplaints(int userID) {
        //sql语句
        String sql = "DELETE FROM `complaint` WHERE `complaint`.userID = ?";
        //获取数据库连接
        try {
            conn = DruidJDBC.getConnection();
            //sql语句执行对象的设置
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, userID);
            int i = pstmt.executeUpdate();
            if (i == 1) {
                return true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidJDBC.close(pstmt, conn);
        }
        return false;
    }

    //删除用户的所有充值单
    public static boolean delRecharges(int userID) {
        //sql语句
        String sql = "DELETE FROM `recharge` WHERE `recharge`.userID = ?";
        //获取数据库连接
        try {
            conn = DruidJDBC.getConnection();
            //sql语句执行对象的设置
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, userID);
            int i = pstmt.executeUpdate();
            if (i == 1) {
                return true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidJDBC.close(pstmt, conn);
        }
        return false;
    }
}
